#/bin/python
import sys
import MySQLdb
from time import time, localtime, strftime
import os

if len(sys.argv) == 2:
	tables_name = sys.argv[1]
else:
	print '[ERROR] you need to input the tables_name.'
	sys.exit(1)

# load tables infomation
print '[INFO] %s load tables infomation.' % strftime('%Y-%m-%d %X', localtime())
tables_info = {}
f = open("tables.info",'r')
for line in f:
	(tablename, useridx, programidx, seriesidx) = line.strip('\n').split(',')
	tables_info[tablename]=[int(useridx),int(programidx),int(seriesidx)]
f.close()

tables_create = {}
f = open("tables.create",'r')
for line in f:
	(tablename, create) = line.strip('\n').split('|')
	tables_create[tablename.strip('`')]=create
f.close()
f = open("tables_map.create",'r')
for line in f:
	(tablename, create) = line.strip('\n').split('|')
	tables_create[tablename.strip('`')]=create
f.close()

# connect to mysql database
print '[INFO] %s connect to mysql database.' % strftime('%Y-%m-%d %X', localtime())
conn_info = {}
f = open("mysql.config",'r')
for line in f:
	(key, val) = line.strip('\n').split('=')
	conn_info[key] = val
f.close()

try:
	conn = MySQLdb.connect(host=conn_info['host'], user=conn_info['user'], passwd=conn_info['passwd'], db=conn_info['db'], init_command=conn_info['init_command'])
	cursor = conn.cursor()
except MySQLdb.Error, e:
	print "Error %d: %s" % (e.args[0], e.args[1])
	sys.exit (1)

# define global variable
delimiter = '|'
step = int(conn_info['step'])

# get the increment id, build the mapping relation
def getid(lst, idx, map_id):
#	if idx >= len(lst):
#		print '%s%s' % (lst[0],lst[1])
	ele = lst[idx].strip('"')
	if ele != '\N':
		if ele not in map_id:
			map_id[ele] =  len(map_id) + 1
#		lst[idx] = ''.join(['"', str(map_id[ele]), '"'])
		lst[idx] = map_id[ele]
	return 1

# load the increment id from database
def loadid(id):
	map_id = {}
	tablename = ''.join(['map_', id])
	try:
		sql = "CREATE TABLE IF NOT EXISTS %s %s" % (tablename,tables_create[tablename])
		cursor.execute(sql)
		sql = "select * from %s" % (tablename)
		cursor.execute(sql)
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])
		sys.exit (1)
	rows = cursor.fetchall()
	for row in rows:
		map_id[row[0]] = row[1]
	return map_id

# def a dump function, which update the data
def dump_sql(map_userid, map_programid, map_seriesid, lines, useridx, programidx, seriesidx):
	useridx -= 1
	programidx -= 1
	seriesidx -= 1

	flag = 0
	if useridx >= 0:
		flag += 4
	if programidx >= 0:
		flag += 2
	if seriesidx >= 0:
		flag += 1

	res = ''
	if flag == 1:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, seriesidx, map_seriesid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 2:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, programidx, map_programid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 3:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, programidx, map_programid)
			getid(lst, seriesidx, map_seriesid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 4:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, useridx, map_userid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 5:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, useridx, map_userid)
			getid(lst, seriesidx, map_seriesid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 6:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, useridx, map_userid)
			getid(lst, programidx, map_programid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 7 and useridx == 0:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, programidx, map_programid)
			getid(lst, seriesidx, map_programid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	elif flag == 7 and useridx == 1:
		for line in lines:
			lst = line.strip('\n').split(delimiter)
			getid(lst, programidx, map_seriesid)
			getid(lst, seriesidx, map_seriesid)
			res += delimiter.join((str(i) for i in lst))
			res += '\n'
	return res #.replace('None','\N')

# load id to map
print '[INFO] %s load id to map.' % strftime('%Y-%m-%d %X', localtime())
map_userid = loadid('userid')
map_programid = loadid('programid')
map_seriesid = loadid('seriesid')

# convert tables [tablename, total, useridx, programidx, seriesidx]
tables = []
f = open(tables_name,'r')
for line in f:
	tablename = line.strip('\n')
	if tablename.find("#") == -1:
		if tablename.find("map") == -1:
			try:
				sql = "select count(*) from %s" % (tablename)
				cursor.execute(sql)
			except MySQLdb.Error, e:
				print "Error %d: %s" % (e.args[0], e.args[1])
				sys.exit (1)
			rows = cursor.fetchall()
			total = int(rows[0][0])
		else:
			total = 0
		useridx = tables_info[tablename][0]
		programidx = tables_info[tablename][1]
		seriesidx = tables_info[tablename][2]
		tables.append([tablename,total,useridx,programidx,seriesidx])
f.close()

# convert every table in tables
for k in range(len(tables)):
	tablename = tables[k][0]
	total = tables[k][1]
	useridx = tables[k][2]
	programidx = tables[k][3]
	seriesidx = tables[k][4]
	if tablename.find("map") == -1:
		sourceFile = ''.join(['/tmp/', tablename, '.csv'])
		resultFile = ''.join([tablename, '_new', '.csv'])
	
		if os.path.exists(sourceFile):
			os.remove(sourceFile)
	
		t = time()
		try:
			sql = "SELECT * INTO OUTFILE '%s' FIELDS TERMINATED BY '%s' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM %s" % (sourceFile,delimiter,tablename)
			cursor.execute(sql)
		except MySQLdb.Error, e:
			print "Error %d: %s" % (e.args[0], e.args[1])
			sys.exit (1)
		print '[INFO] %s cost %f, load data from %s' % (strftime('%Y-%m-%d %X', localtime()), time()-t, tablename)
	
		inFile = file(sourceFile, 'r')
		outFile = file(resultFile, 'w')
		n = 0
#		for i in range(0,100000):
		while 1:
			t = time()
			tmp_lines = inFile.readlines(step)
			length = len(tmp_lines)
			if length == 0:
				break
			n += length
			outFile.write(dump_sql(map_userid, map_programid, map_seriesid, tmp_lines, useridx, programidx, seriesidx))
			print '[INFO] %s cost %f, convert %d finish %d total %d,\tconvert table %s' % (strftime('%Y-%m-%d %X', localtime()), time()-t, length, n, total,tablename)
			sys.stdout.flush()
		inFile.close()
		outFile.close()
		os.remove(sourceFile)

# write map_userid, map_programid, map_seriesid to file
f = file('map_userid_new.csv', 'w')
f.write(''.join([('%s%s%s' % (userid, delimiter, map_userid[userid]) + '\n') for userid in map_userid]))
f.close()

f = file('map_programid_new.csv', 'w')
f.write(''.join([('%s%s%s' % (programid, delimiter, map_programid[programid]) + '\n') for programid in map_programid]))
f.close()

f = file('map_seriesid_new.csv', 'w')
f.write(''.join([('%s%s%s' % (seriesid, delimiter, map_seriesid[seriesid]) + '\n') for seriesid in map_seriesid]))
f.close()

# load data to mysql
filedir = os.getcwd()
for k in range(len(tables)):
        tablename = ''.join([tables[k][0], '_new'])
        filename = ''.join([filedir, '/', tablename, '.csv'])

        t = time()
        sql = "DROP TABLE IF EXISTS %s" % (tablename)
        cursor.execute(sql)
        sql = "CREATE TABLE %s %s" % (tablename, tables_create[tables[k][0]])
        cursor.execute(sql)
        sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s CHARACTER SET UTF8 FIELDS TERMINATED BY '%s' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" % (filename, tablename, delimiter)
        cursor.execute(sql)
	if tablename.find("map") == -1:
		os.remove(filename)
	else:
		str_mv_file = ''.join(['mv ', filename, ' /home/bestv/cmsdata/map_table', '/', tablename, strftime('%Y%m%d%H%M%S', localtime()), '.csv'])
		os.system(str_mv_file)		
        print '[INFO] %s cost %f, load data to %s' % (strftime('%Y-%m-%d %X', localtime()), time()-t, tablename)
        sys.stdout.flush()

# rename tables
for k in range(len(tables)):
	tablename = tables[k][0]

	t = time()	
	sql = "DROP TABLE IF EXISTS %s_old" % (tablename)
	cursor.execute(sql)
	sql = "ALTER TABLE %s RENAME AS %s_old" % (tablename, tablename)
	cursor.execute(sql)
	sql = "ALTER TABLE %s_new RENAME AS %s" % (tablename, tablename)
	cursor.execute(sql)
	print '[INFO] %s cost %f, rename table %s' % (strftime('%Y-%m-%d %X', localtime()), time()-t, tablename)
	sys.stdout.flush()

print '[INFO] %s the convert task is completed.' % strftime('%Y-%m-%d %X', localtime())
